﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DocumentFormat.OpenXml;
using SpreadsheetLight;
using SpreadsheetLight.Drawing;
using System.Text;
using DocumentFormat.OpenXml.Wordprocessing;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Web.SessionState;
using System.Data;
using System.IO;
using SpreadsheetLight.Charts;

namespace ReportTracking.Services.Report
{
    /// <summary>
    /// Summary description for PL2
    /// </summary>
    public class PL2 : IHttpHandler, IReadOnlySessionState
    {
        string sPath = "";
        public void ProcessRequest(HttpContext context)
        {
            try
            {
                sPath = HttpContext.Current.Request.PhysicalApplicationPath;
                context.Response.ContentType = "text/plain";
                int nIndexRow = 0;
                int nIndexColumn = 0;
                int nColumn = 13;
                string charEnd = "M";
                InfoReport inforeport = (InfoReport)context.Session["InfoReprot"];

                SLDocument sl = new SLDocument();
                sl.RenameWorksheet(SLDocument.DefaultFirstSheetName, "ThongKe");
                System.Drawing.Bitmap bm = new System.Drawing.Bitmap(sPath + "Images/logo/VBDlogo.png");
                byte[] ba;
                using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
                {
                    bm.Save(ms, System.Drawing.Imaging.ImageFormat.Png);
                    ms.Close();
                    ba = ms.ToArray();
                }
                SLPicture pic = new SLPicture(ba, DocumentFormat.OpenXml.Packaging.ImagePartType.Png);
                pic.SetPosition(nIndexRow, nIndexColumn);
                sl.InsertPicture(pic);

                SLStyle style = sl.CreateStyle();
                style.Alignment.Horizontal = DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Justify;
                style.Font.FontName = "Arial";
                style.Font.FontSize = 30;
                style.Font.FontColor = System.Drawing.Color.Blue;
                style.Font.Bold = true;
                sl.SetCellValue("C1", "Thống kê vi phạm về tốc độ xe chạy");
                sl.SetCellStyle("C1", style);
                sl.MergeWorksheetCells("C1", charEnd + "4");

                style.Font.FontName = "Arial";
                style.Font.FontSize = 12;
                style.Font.FontColor = System.Drawing.Color.Crimson;
                style.Font.Bold = true;

                if (inforeport != null)
                {
                    StringBuilder sHD = new StringBuilder();
                    sHD.Append("Thống kê vi phạm về tốc độ xe chạy ");
                    //sHD.Append("60 km/h ");
                    sHD.Append(". Từ ngày: ");
                    sHD.Append(inforeport.FromDay);
                    sHD.Append(" đến ");
                    sHD.Append(inforeport.ToDay);

                    nIndexRow = nIndexRow + 6;
                    nIndexColumn++;

                    sl.MergeWorksheetCells("A6", charEnd + "6");
                    sl.SetCellValue(nIndexRow, nIndexColumn, sHD.ToString());
                    sl.SetCellStyle(nIndexRow, nIndexColumn, style);
                    nIndexRow++;
                    sHD = new StringBuilder();
                    style.Font.FontColor = System.Drawing.Color.MediumBlue;
                    sHD.Append("Đơn vị kinh doanh vận tải:");
                    if (inforeport.DVKD == null)
                    {
                        sHD.Append("Tất cả");

                    }
                    else
                    {
                        if (inforeport.DVKD.Length > 1)
                        {
                            for (int i = 0; i < inforeport.DVKD.Length; i++)
                            {
                                sHD.Append(inforeport.DVKD[i]);
                                sHD.Append(";");
                            }
                            sHD.Replace(";", ".", sHD.Length - 2, 1);
                        }
                        else if (inforeport.DVKD.Length > 0)
                        {
                            sHD.Append(inforeport.DVKD[0]);
                        }
                    }
                    sl.MergeWorksheetCells("A7", charEnd + "7");
                    sl.SetCellValue(nIndexRow, nIndexColumn, sHD.ToString());
                    sl.SetCellStyle(nIndexRow, nIndexColumn, style);
                }

                nIndexRow++;
                style.SetWrapText(true);
                style.Font.FontSize = 10;
                style.Font.FontColor = System.Drawing.Color.Navy;

                style.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
                style.Border.LeftBorder.Color = System.Drawing.Color.YellowGreen;
                style.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
                style.Border.TopBorder.Color = System.Drawing.Color.YellowGreen;
                style.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
                style.Border.RightBorder.Color = System.Drawing.Color.YellowGreen;
                style.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
                style.Border.BottomBorder.Color = System.Drawing.Color.YellowGreen;

                sl.SetCellStyle(nIndexRow, nIndexColumn, (nIndexRow + 1), nColumn, style);
                sl.MergeWorksheetCells("A" + nIndexRow, "A" + (nIndexRow + 1));
                sl.SetCellValue(nIndexRow, nIndexColumn, "STT");


                sl.MergeWorksheetCells("B" + nIndexRow, "B" + (nIndexRow + 1));
                sl.SetCellValue(nIndexRow, (nIndexColumn + 1), "Biển kiểm soát");

                sl.MergeWorksheetCells("C" + nIndexRow, "C" + (nIndexRow + 1));
                sl.SetCellValue(nIndexRow, (nIndexColumn + 2), "Loại hình hoạt động");

                sl.MergeWorksheetCells("D" + nIndexRow, "D" + (nIndexRow + 1));
                sl.SetCellValue(nIndexRow, (nIndexColumn + 3), "Đơn vị quản lý");

                sl.MergeWorksheetCells("E" + nIndexRow, "E" + (nIndexRow + 1));
                sl.SetCellValue(nIndexRow, (nIndexColumn + 4), "Đơn vị kinh doanh");

                sl.MergeWorksheetCells("F" + nIndexRow, "F" + (nIndexRow + 1));
                sl.SetCellValue(nIndexRow, (nIndexColumn + 5), "Họ tên lái xe");

                sl.MergeWorksheetCells("G" + nIndexRow, "G" + (nIndexRow + 1));
                sl.SetCellValue(nIndexRow, (nIndexColumn + 6), "Số giấy phép lái xe");

                sl.MergeWorksheetCells("H" + nIndexRow, "H" + (nIndexRow + 1));
                sl.SetCellValue(nIndexRow, (nIndexColumn + 7), "Ngày hoạt động");

                sl.MergeWorksheetCells("I" + nIndexRow, "I" + (nIndexRow + 1));
                sl.SetCellValue(nIndexRow, (nIndexColumn + 8), "Tổng km hoạt động trong ngày");

                //nIndexRow++; //+ nIndexRow =8

                sl.MergeWorksheetCells("J" + nIndexRow, "L" + nIndexRow);
                sl.AutoFitRow(nIndexRow, 30d);
                sl.SetCellValue("J" + nIndexRow, "Tổng km xe chạy quá.... km/h(km)");
                nIndexRow++; //+ nIndexRow =9
                sl.SetCellValue("J" + nIndexRow, "Số lần quá tốc từ 10km");
                sl.SetCellValue("K" + nIndexRow, "Số lần quá tốc từ 10km đến 20km");
                sl.SetCellValue("L" + nIndexRow, "Số lần quá tốc từ 10km");

                /*sl.MergeWorksheetCells("M7", "O7");
                sl.SetCellValue("M7", "Tổng km xe chạy quá.... km/h(km)");

                sl.SetCellValue("M8", "Tỷ lệ quá < 10km");
                sl.SetCellValue("N8", "Số lần quá từ 10km đến 20km");
                sl.SetCellValue("O8", "Tỷ lệ quá > 20km");*/
                nIndexRow--;//nIndexRow = 8
                sl.MergeWorksheetCells("M" + nIndexRow, "M" + (nIndexRow + 1));
                sl.SetCellValue(nIndexRow, (nIndexColumn + nColumn - 1), "Ghi chú");

                style.Font.FontSize = 10;
                style.Font.FontColor = System.Drawing.Color.MediumBlue;
                style.Font.Bold = false;

                nIndexRow = nIndexRow + 2;
                DataTable tb = (DataTable)context.Session["Data"];
                int nStartStyle = nIndexRow;
                if (tb != null)
                {
                    int nTotal = tb.Rows.Count;

                    for (int r = 0; r < nTotal; r++)
                    {
                        sl.SetCellValue(nIndexRow, nIndexColumn, (r + 1));
                        sl.SetCellValue(nIndexRow, nIndexColumn + 1, tb.Rows[r]["PlateNumber"].ToString());
                        sl.SetCellValue(nIndexRow, nIndexColumn + 2, tb.Rows[r]["BusinessUnitType"].ToString());
                        sl.SetCellValue(nIndexRow, nIndexColumn + 3, "");
                        sl.SetCellValue(nIndexRow, nIndexColumn + 4, tb.Rows[r]["BusinessUnit"].ToString());
                        sl.SetCellValue(nIndexRow, nIndexColumn + 5, tb.Rows[r]["DriverName"].ToString());
                        sl.SetCellValue(nIndexRow, nIndexColumn + 6, tb.Rows[r]["LicenseNumber"].ToString());
                        sl.SetCellValue(nIndexRow, nIndexColumn + 7, String.Format("{0:d/M/yyyy}", tb.Rows[r]["Date"].ToString()));
                        sl.SetCellValueNumeric(nIndexRow, nIndexColumn + 8, String.Format("{0:0.00}", tb.Rows[r]["TotalDistance"]));
                        sl.SetCellValueNumeric(nIndexRow, nIndexColumn + 9, tb.Rows[r]["Less10Km"].ToString());
                        sl.SetCellValueNumeric(nIndexRow, nIndexColumn + 10, tb.Rows[r]["Between10Km20Km"].ToString());
                        sl.SetCellValueNumeric(nIndexRow, nIndexColumn + 11, tb.Rows[r]["Great20Km"].ToString());
                        sl.SetCellValue(nIndexRow, nIndexColumn + 12, tb.Rows[r]["Note"].ToString());
                        nIndexRow++;
                    }
                    sl.SetCellStyle(nStartStyle, nIndexColumn, nStartStyle + nTotal - 1, nIndexColumn + 12, style);
                    sl.AutoFitRow(6, nTotal);
                }

                //Set Sheet thu 2
                sl.AddWorksheet("Chart");

                /* Lay du lieu o day */
                var results = from myRow in tb.AsEnumerable()
                              group myRow by myRow.Field<string>("BusinessUnitType") into g
                              select new { LHDV = g.Key, Numbers = g.Count() };
                System.Collections.Hashtable htb = new System.Collections.Hashtable();

                foreach (var r in results)
                {
                    htb.Add(r.LHDV, r.Numbers);

                }
                /* KT lay du lieu o day*/

                int nRowIndexChart = 3;
                int nColumnIndexChart = 3;
                System.Collections.ArrayList coll = new System.Collections.ArrayList(htb.Keys);
                if (htb.Count >0)
                {
                    
                    for (int i = 0; i < coll.Count; i++)
                    {
                        sl.SetCellValue(nRowIndexChart, nColumnIndexChart + i, coll[i].ToString());
                        sl.SetCellValueNumeric(nRowIndexChart + 1, nColumnIndexChart + i, htb[coll[i]].ToString());
                    }
                    sl.SetCellValue(nRowIndexChart + 1, nColumnIndexChart - 1, "Loại hình hoạt động");
                 }
                
                double fChartHeight = 15.0;
                double fChartWidth = 7.5;
                SLChart chart;
                chart = sl.CreateChart(nRowIndexChart, nColumnIndexChart-1, nRowIndexChart + 1,
                                       nColumnIndexChart + htb.Count - 1);
                chart.SetChartType(SLPieChartType.ExplodedPie3D);
                chart.SetChartStyle(SLChartStyle.Style10);
                chart.SetChartPosition(8, 2, 8 + fChartHeight, 2 + fChartWidth);
                sl.InsertChart(chart);

                sl.SelectWorksheet("ThongKe");
                //Save file
                string sFolderName = DateTime.Today.ToShortDateString().Replace("/", "");
                Utilities.DeleteFolder(sPath + "FileExcel/", sFolderName);
                string sFileName = Guid.NewGuid().ToString();
                sl.SaveAs(sPath + "FileExcel/" + sFolderName + "/" + sFileName + ".xlsx");

                context.Response.Write(sFileName);
            }
            catch (Exception)
            {
                
                throw;
            }
            
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}